'=====================================================================
'  File:       Main.vb
'
'  Summary:    This file contains the builds a package programmatically. It adds a Data flow task 
'              to the Executables collection, and adds three Data Flow components
'              (OLEDB Source, Sort, and Flat File Destination) 
'              to the data flow ComponentMetaDataCollection.
'              
'  Date:       5/2005
'
'---------------------------------------------------------------------
'  This file is part of the Microsoft SQL Server Code Samples.
'  Copyright (C) Microsoft Corporation.  All rights reserved.
'
'  This source code is intended only as a supplement to Microsoft
'  Development Tools and/or on-line documentation.  See these other
'  materials for detailed information regarding Microsoft code samples.
'
'  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
'  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
'  THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'  PARTICULAR PURPOSE.
'=====================================================================

Imports System
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime              '  Managed Runtime namespace, Microsoft.SqlServer.ManagedDTS.dll
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper     '  Pipeline Primary Interop Assembly, Microsoft.SqlServer.DTSPipelineWrap.dll
Imports wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper

Namespace Microsoft.Samples.SqlServer.SSIS
    Public Class OleDBToFlatFile
        Const PACKAGEFILENAME As String = "\SampleRuntimePackage.dtsx"
        Const DATAFILENAME As String = "\SampleRuntimeData.txt"
        Const LOGFILENAME As String = "\SampleRuntimeLog.log"

        Private package As Package                       '  package object
        Private dataFlow As MainPipe                     '	dataFlow task object
        Private oledbSource As IDTSComponentMetaData100   '	OLEDB dataFlow component object
        Private sort As IDTSComponentMetaData100          '   Sort transform dataFlow component object
        Private flatfileDestination As IDTSComponentMetaData100  '	FlatFile dataFlow component object
        Private packageEvents As PackageEvents                  '	class that implements the Package events interface IDTSEvents100
        Private pipelineEvents As ComponentEvents    '    class that implements the component events interface IDTSComponentEvents100

        Public Sub New(ByVal sortColumnName As String)
            If sortColumnName = String.Empty Then
                sortColumnName = "Name"
            End If

            Dim currentDirectory As String = Directory.GetCurrentDirectory()

            ' Delete the log file, destination, and package files.
            File.Delete(currentDirectory & LOGFILENAME)
            File.Delete(currentDirectory & DATAFILENAME)
            File.Delete(currentDirectory & PACKAGEFILENAME)

            package = CreatePackage("SSISPackage", "The package for the SSIS CreatePackage sample")
            package.Variables.Add("SortColumn", False, "", SortColumnName)

            ' Events.
            packageEvents = New PackageEvents()
            pipelineEvents = New ComponentEvents()

            ' Add the Runtime objects.
            AddLogging(currentDirectory & LOGFILENAME)
            AddConnectionManagers(currentDirectory)
            AddDataFlowTask()

            ' Add the DataFlow components.
            AddOLEDBSource()
            AddSort()
            AddFlatFileDestination()

            ' Validate the layout of the package.
            Dim status As DTSExecResult = package.Validate(Nothing, Nothing, packageEvents, Nothing)
            System.Console.WriteLine("Validation result: " & status)

            ' Save the package
            Dim a As New Application()
            a.SaveToXml(currentDirectory & PACKAGEFILENAME, package, packageEvents)
            Console.WriteLine("Package saved to " & currentDirectory & PACKAGEFILENAME)

            ' If the package validated successfully, then execute it.
            If status = DTSExecResult.Success Then
                Console.WriteLine("Beginning package execution.")

                ' Execute the package
                Dim result As DTSExecResult = package.Execute(Nothing, Nothing, packageEvents, Nothing, Nothing)

                Console.WriteLine("Execution result: " & result)
            End If

            If File.Exists(currentDirectory & DATAFILENAME) Then
                Console.WriteLine("Sample data saved to " & currentDirectory & DATAFILENAME)
            End If
        End Sub

        ''' <summary>
        ''' Create the package and assign it some default properties.
        ''' </summary>
        Private Shared Function CreatePackage(ByVal Name As String, ByVal Description As String) As Package
            Dim p As New Package()
            p.PackageType = DTSPackageType.DTSDesigner100
            p.Name = Name
            p.Description = Description
            p.CreatorComputerName = System.Environment.MachineName
            p.CreatorName = System.Environment.UserName
            Return p
        End Function

        ''' <summary>
        ''' Enable package level logging.
        ''' </summary>
        Private Sub AddLogging(ByVal Path As String)
            Try
                package.LoggingMode = DTSLoggingMode.Enabled

                ' Add a file connection manager for the text log provider.
                Dim cm As ConnectionManager = package.Connections.Add("FILE")

                cm.ConnectionString = Path
                cm.Name = "FileLogProviderConnection"

                ' Add a LogProvider.
                Dim provider As LogProvider = package.LogProviders.Add("DTS.LogProviderTextFile")

                provider.ConfigString = cm.Name
                package.LoggingOptions.SelectedLogProviders.Add(provider)
            Catch nre As System.NullReferenceException
                System.Diagnostics.Debug.WriteLine(nre.StackTrace)
            End Try
        End Sub

        ''' <summary>
        ''' Adds the OLEDB and FlatFile connection managers to the package.
        ''' </summary>
        Private Sub AddConnectionManagers(ByVal DestinationDataDirectory As String)
            ' Add the OLEDB connection manager.
            Dim adventureWorks As ConnectionManager = package.Connections.Add("OLEDB")

            ' Set stock properties.
            adventureWorks.Name = "OLEDBConnection"
            adventureWorks.ConnectionString = "Provider=sqlncli10;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=(local);Auto Translate=False;"

            ' Add the Destination connection manager.
            Dim cmflatFile As ConnectionManager = package.Connections.Add("FLATFILE")

            ' Set the stock properties.
            cmflatFile.Properties("ConnectionString").SetValue(cmflatFile, DestinationDataDirectory & DATAFILENAME)
            cmflatFile.Properties("Format").SetValue(cmflatFile, "Delimited")
            cmflatFile.Properties("DataRowsToSkip").SetValue(cmflatFile, 0)
            cmflatFile.Properties("ColumnNamesInFirstDataRow").SetValue(cmflatFile, False)
            cmflatFile.Properties("Name").SetValue(cmflatFile, "FlatFileConnection")
            cmflatFile.Properties("RowDelimiter").SetValue(cmflatFile, vbCrLf)
            cmflatFile.Properties("TextQualifier").SetValue(cmflatFile, """")
        End Sub

        ''' <summary>
        ''' Adds a DataFlow task to the Executables collection of the package.
        ''' Retrieves the MainPipe object from the TaskHost and stores it in 
        ''' the dataFlow member variable.
        ''' </summary>
        Private Sub AddDataFlowTask()
            Dim th As TaskHost = TryCast(package.Executables.Add("DTS.Pipeline"), TaskHost)
            th.Name = "DataFlow"
            th.Description = "The DataFlow task in the DTSAuto sample."

            dataFlow = TryCast(th.InnerObject, MainPipe)
            dataFlow.Events = TryCast(pipelineEvents, wrap.IDTSComponentEvents100)
        End Sub

        ''' <summary>
        ''' Adds the OLEDB Data Source component to the DataFlow task.
        ''' Creates an instance of the component.
        ''' Sets the runtime connection manager.
        ''' Sets two custom properties; the SqlCommand, and ValidateColumnMetaData.
        ''' Acquires the connection and Reinitializes the metadata. 
        ''' </summary>
        Private Sub AddOLEDBSource()
            oledbSource = dataFlow.ComponentMetaDataCollection.New()

            ' Set stock properties.
            oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource"
            oledbSource.Name = "OLEDBSource"
            oledbSource.Description = "Source data in the dataFlow"

            Dim instance As CManagedComponentWrapper = oledbSource.Instantiate()
            instance.ProvideComponentProperties()

            ' Associate the runtime connection manager
            '  The connection manager association will fail if called before ProvideComponentProperties
            oledbSource.RuntimeConnectionCollection(0).ConnectionManagerID _
                = package.Connections("OLEDBConnection").ID
            oledbSource.RuntimeConnectionCollection(0).ConnectionManager _
                = DtsConvert.ToConnectionManager90(package.Connections("OLEDBConnection"))

            ' set custom component properties
            instance.SetComponentProperty("OpenRowset", "[Production].[Product]")
            instance.SetComponentProperty("AccessMode", 0)

            ' Acquire Connections and reinitialize the component
            instance.AcquireConnections(Nothing)
            instance.ReinitializeMetaData()
            instance.ReleaseConnections()
        End Sub

        ''' <summary>
        ''' Adds the DTSTransform.Sort to the DataFlow task.
        ''' </summary>
        Private Sub AddSort()
            ' Add the component to the DataFlow task.
            sort = dataFlow.ComponentMetaDataCollection.New()

            ' Set component's stock properties.
            sort.ComponentClassID = "DTSTransform.Sort"
            sort.Name = "SortTransform"
            sort.Description = "Sort component"

            Dim instance As CManagedComponentWrapper = sort.Instantiate()
            instance.ProvideComponentProperties()

            ' Attach path between the OLEDB source components Output, and the Sort Component's Input.
            dataFlow.PathCollection.New().AttachPathAndPropagateNotifications( _
                oledbSource.OutputCollection(0), sort.InputCollection(0))

            Dim vInput As IDTSVirtualInput100 = sort.InputCollection(0).GetVirtualInput()

            Dim vColumn As IDTSVirtualInputColumn100
            For Each vColumn In vInput.VirtualInputColumnCollection
                Dim col As IDTSInputColumn100 = instance.SetUsageType(sort.InputCollection(0).ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

                If col.Name = package.Variables("SortColumn").Value.ToString() Then
                    If col.CustomPropertyCollection.Count > 0 Then
                        instance.SetInputColumnProperty(sort.InputCollection(0).ID, col.ID, "NewSortKeyPosition", 1)
                    End If
                End If
            Next
        End Sub


        Private Sub AddFlatFileDestination()
            ' Add the component to the dataFlow metadata collection
            flatfileDestination = dataFlow.ComponentMetaDataCollection.New()

            ' Set the common properties
            flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination"
            flatfileDestination.Name = "FlatFileDestination"
            flatfileDestination.Description = "Flat file destination"

            ' Create an instance of the component
            Dim inst As CManagedComponentWrapper = flatfileDestination.Instantiate()
            inst.ProvideComponentProperties()

            ' Associate the runtime ConnectionManager with the component
            flatfileDestination.RuntimeConnectionCollection(0).ConnectionManagerID _
                = package.Connections("FlatFileConnection").ID
            flatfileDestination.RuntimeConnectionCollection(0).ConnectionManager _
                = DtsConvert.ToConnectionManager90( _
                package.Connections("FlatFileConnection"))

            ' Map a path between the Sort transformation component to the FlatFileDestination
            dataFlow.PathCollection.New().AttachPathAndPropagateNotifications( _
                sort.OutputCollection(0), flatfileDestination.InputCollection(0))

            ' Add columns to the FlatFileConnectionManager
            AddColumnsToFlatFileConnection()

            ' Acquire the connection, reinitialize the metadata, 
            ' map the columns, then release the connection.
            inst.AcquireConnections(Nothing)
            inst.ReinitializeMetaData()
            MapFlatFileDestinationColumns()
            inst.ReleaseConnections()
        End Sub

        Private Sub MapFlatFileDestinationColumns()
            Dim wrp As CManagedComponentWrapper = flatfileDestination.Instantiate()

            Dim vInput As IDTSVirtualInput100 = flatfileDestination.InputCollection(0).GetVirtualInput()

            For Each vColumn As IDTSVirtualInputColumn100 In vInput.VirtualInputColumnCollection
                wrp.SetUsageType(flatfileDestination.InputCollection(0).ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
            Next

            ' For each column in the input collection
            ' find the corresponding external metadata column.
            Dim exCol As IDTSExternalMetadataColumn100
            For Each col As IDTSInputColumn100 In flatfileDestination.InputCollection(0).InputColumnCollection
                exCol = flatfileDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)
                wrp.MapInputColumn(flatfileDestination.InputCollection(0).ID, col.ID, exCol.ID)
            Next
        End Sub

        Private Sub AddColumnsToFlatFileConnection()
            Dim ff As wrap.IDTSConnectionManagerFlatFile100 = Nothing

            For Each cm As ConnectionManager In package.Connections
                If cm.Name = "FlatFileConnection" Then
                    ff = TryCast(cm.InnerObject, wrap.IDTSConnectionManagerFlatFile100)
                    DtsConvert.ToConnectionManager90(cm)
                End If
            Next

            ' if the connection manager is null here, then we have a problem
            If Not ff Is Nothing Then
                ' Get the upstream columns
                Dim vColumns As IDTSVirtualInputColumnCollection100 _
                    = flatfileDestination.InputCollection(0).GetVirtualInput().VirtualInputColumnCollection

                Dim col As wrap.IDTSConnectionManagerFlatFileColumn100
                Dim name As wrap.IDTSName100
                For cols As Integer = 0 To vColumns.Count - 1 Step 1
                    col = ff.Columns.Add()

                    ' If this is the last column, set the delimiter to CRLF.
                    ' Otherwise keep the delimiter as ",".
                    If cols = vColumns.Count - 1 Then
                        col.ColumnDelimiter = vbCrLf
                    Else
                        col.ColumnDelimiter = ","
                    End If

                    col.ColumnType = "Delimited"
                    col.DataType = vColumns(cols).DataType
                    col.DataPrecision = vColumns(cols).Precision
                    col.DataScale = vColumns(cols).Scale
                    name = TryCast(col, wrap.IDTSName100)
                    name.Name = vColumns(cols).Name
                Next
            End If
        End Sub
    End Class
End Namespace
